package com.scuec.groupfive.mapper;

import com.scuec.groupfive.vo.CorrectStatisticsVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface CorrectStatisticsMapper {
    @Select("SELECT\n" +
            "  DATE_FORMAT(ar.answered_at, '%Y-%m') AS analysis_month,\n" +
            "  s.subject_name AS subject,\n" +
            "  COUNT(DISTINCT IF(ar.question_type IN ('single_choice', 'fill_in_the_blank'), ar.question_id, NULL)) AS total_questions, \n" +
            "  COUNT(DISTINCT IF(ar.is_correct = TRUE AND ar.question_type IN ('single_choice', 'fill_in_the_blank'), ar.question_id, NULL)) AS correct_questions, \n" +
            "  ROUND(\n" +
            "    IF(\n" +
            "      COUNT(DISTINCT IF(ar.question_type IN ('single_choice', 'fill_in_the_blank'), ar.question_id, NULL)) = 0,\n" +
            "      0,\n" +
            "      (COUNT(DISTINCT IF(ar.is_correct = TRUE AND ar.question_type IN ('single_choice', 'fill_in_the_blank'), ar.question_id, NULL)) /\n" +
            "      COUNT(DISTINCT IF(ar.question_type IN ('single_choice', 'fill_in_the_blank'), ar.question_id, NULL)) * 100)\n" +
            "    ),\n" +
            "    2\n" +
            "  ) AS accuracy_rate\n" +
            "FROM\n" +
            "  answer_records ar\n" +
            "JOIN subjects s ON ar.subject_id = s.subject_id\n" +
            "WHERE\n" +
            "  ar.user_id = #{userId} \n" +
            "  AND s.subject_name = #{subjectName} \n" +
            "  AND YEAR(ar.answered_at) = #{year} \n" +
            "  AND MONTH(ar.answered_at) BETWEEN #{startMonth} AND #{endMonth} \n" +
            "  AND ar.question_type IN ('single_choice', 'fill_in_the_blank') \n" +
            "GROUP BY\n" +
            "  DATE_FORMAT(ar.answered_at, '%Y-%m'), s.subject_name\n" +
            "ORDER BY\n" +
            "  analysis_month, subject;")
    List<CorrectStatisticsVO> getSubjectCorrectTrend(
            @Param("userId") Integer userId,
            @Param("subjectName") String subjectName,
            @Param("year") Integer year,
            @Param("startMonth") Integer startMonth,
            @Param("endMonth") Integer endMonth
    );
}